package com.hnkywl.sts.dao.jhgl;

import com.hnkywl.sts.entity.jhgl.Bgjhmx;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ZHUCHG
 * Date: 13-6-26
 * Time: 下午8:57
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BgjhmxDao extends SimpleHibernateTemplate<Bgjhmx, Long> {
    // 估计计划id，查询保供计划明细
    public List<Map> findList(Long id) {
        String sql = "select tg.code  \"wzgg.code\",tg.oldcode \"wzgg.oldcode\",tg.name  \"wzgg.name\", tg.ggxh \"wzgg.ggxh\",tg.dw \"dw\",tm.jhsl \"jhsl\",sz.z \"cd.z\",sz.id \"cd.id\",\n" +
                "                tm.dhrq  \"dhrq\",tm.ph  \"ph\",tm.sbcl \"sbcl\",(select xm from t_sys_yg ty where ty.id = tb.createuser_id) \"jhy\",\n" +
                "                decode(tb.zt,1,'未提交',2,'已提交',3,'已汇总')  \"zt\",tm.bz \"bz\" \n" +
                "                from t_biz_bgjh tb, t_biz_bgjhmx tm, t_sys_gg tg ,t_sys_sjzdz sz\n" +
                "                where tm.bgjh_id = tb.id and tg.id = tm.wzgg_id and sz.id = tm.sjzdz_cd_id(+)\n" +
                "                  and tm.deleted!=1 and tb.deleted!=1 and tg.deleted!=1 and tm.bgjh_id=? order by tg.oldcode";
        List<Map> result = createSQLQuery(sql, id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }

    public List<Map> collectBgjhhzmx(List<Long> id) {
        String sql = "select x.wzgg_id as \"wzgg.id\",\n" +
                "       x.sbcl as \"sbcl\",\n" +
                "       x.dhrq as \"dhrq\",\n" +
                "       x.ph as \"ph\",\n" +
                "       x.bz as \"bz\",\n" +
                "       x.hsj as \"hsj\",\n" +
                "       x.hse as \"hse\",\n" +
                "       x.xsxt_htbm as \"xsxt_htbm\",\n" +
                "       x.xsxt_xsdh as \"xsxt_xsdh\",\n" +
                "       x.sfhdj as \"sfhdj\",\n" +
                "       x.sjzdz_cd_id as \"cd.id\",\n" +
                "       x.jhsl as \"jhsl\",\n" +
                "       x.jhjs as \"jhjs\"\n" +
                "  from (select mx.wzgg_id,\n" +
                "               mx.sbcl,\n" +
                "               (case\n" +
                "                 when ltrim(rtrim(mx.ph)) is not null then\n" +
                "                  mx.ph || '(' || mx.jhsl || g.dw || ')'\n" +
                "               end) ph,\n" +
                "               mx.sjzdz_cd_id,\n" +
                "               mx.jhjs,\n" +
                "               mx.bz,\n" +
                "               mx.hsj,\n" +
                "               mx.hse,\n" +
                "               mx.xsxt_htbm,\n" +
                "               mx.xsxt_xsdh,\n" +
                "               mx.sfhdj,\n" +
                "               mx.jhsl,\n" +
                "               zddhrq.dhrq\n" +
                "          from t_biz_bgjhmx mx\n" +
                "          left join t_sys_gg g\n" +
                "            on mx.wzgg_id = g.id\n" +
                "          left join (select m.wzgg_id, max(m.dhrq) dhrq\n" +
                "                      from t_biz_bgjhmx m\n" +
                "                     where m.bgjh_id in (:id)\n" +
                "                     group by m.wzgg_id) zddhrq\n" +
                "            on zddhrq.wzgg_id = mx.wzgg_id\n" +
                "         where mx.bgjh_id in (:id)\n" +
                "           and mx.zt != 2\n" +
                "           and mx.deleted = 0) x";
        List<Map> list = createSQLQuery(sql)
                .setParameterList("id", id)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

//    public List<String> ValidatacollectBgjhhzmx(List<Long> id) {
//        String sql = " select gg.oldcode from t_sys_gg gg,(select mx.wzgg_id  as wzgg_id, " +
//                "                 mx.sbcl as \"sbcl\",sum(mx.jhsl)  as \"jhsl\", " +
//                "                 mx.dhrq as \"dhrq\"   " +
//                "                 from t_biz_bgjhmx mx where mx.bgjh_id   in (:id)  and mx.zt!=2  and mx.deleted =0 " +
//                "  group by  mx.wzgg_id,mx.sbcl,mx.dhrq " +
//                "  having count(mx.wzgg_id)>1 ) b where b.wzgg_id  = gg.id ";
//        return createSQLQuery(sql).setParameterList("id", id).list();
//    }


    public List<Object> findJhmxByHzmx(Long ggid, Long hzmxId) {
        String sql = "select mx.id from t_biz_bgjh jh,t_biz_bgjhmx mx,t_biz_bgjhhz hz,t_biz_bgjhhzmx hzmx " +
                " where jh.bgjhhz_id=hz.id and jh.id=mx.bgjh_id and hz.id=hzmx.bgjhhz_id and mx.wzgg_id=hzmx.wzgg_id " +
                " and hzmx.wzgg_id=? and hzmx.id=? and mx.deleted!=1";
        List<Object> list = createSQLQuery(sql, ggid, hzmxId).list();
        return list;
    }

    public List<Map> findBgjhmxByBgjhId(Long bgjhId, Long hz) {
        String sql = new String();
        if (hz == 0) {
            sql = "select mx.id as \"id\", " +
                    "g.id as \"wzgg.id\", " +
                    "g.oldcode as \"wzgg.oldcode\", " +
                    "g.code as \"wzgg.code\", " +
                    "g.name as \"wzgg.name\", " +
                    "g.ggxh as \"wzgg.ggxh\", " +
//                    "cd.id as \"cd.id\"," +
//                    "cd.z \"cd.z\", " +
                    "(case when mx.sbcl='sb' then '设备' else '材料' end) as \"sbcl\", " +
                    "'' as \"ckj\", " +
                    "g.dw as \"dw\", " +
                    "mx.jhsl as \"jhsl\", " +
                    "mx.dhrq as \"dhrq\", " +
                    "mx.bz as \"bz\" " +
                    "from t_biz_bgjhmx mx " +
                    "left join t_sys_gg g on mx.wzgg_id=g.id " +
//                    "left join t_sys_sjzdz cd on cd.id=mx.sjzdz_cd_id " +
                    "where mx.bgjh_id=? order by g.oldcode";
        } else if (hz == 1) {
            sql = "select mx.id as \"id\", " +
                    "g.id as \"wzgg.id\", " +
                    "g.oldcode as \"wzgg.oldcode\", " +
                    "g.code as \"wzgg.code\", " +
                    "g.name as \"wzgg.name\", " +
                    "g.ggxh as \"wzgg.ggxh\", " +
                    "(case when mx.sbcl='sb' then '设备' else '材料' end) as \"sbcl\", " +
                    "'' as \"ckj\", " +
                    "g.dw as \"dw\", " +
                    "mx.jhsl as \"jhsl\", " +
                    "mx.dhrq as \"dhrq\", " +
                    "mx.bz as \"bz\" " +
                    " from t_biz_bgjhhzmx mx\n" +
                    " left join t_sys_gg g on g.id = mx.wzgg_id\n" +
                    " left join t_biz_bgjhhz hz on hz.id = mx.bgjhhz_id\n" +
                    " where  hz.id = ? order by g.oldcode";
        }

        List<Map> list = createSQLQuery(sql, bgjhId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return list;
    }

//    public Page<Map> findBgjhmxByBgjhIdAndHz(Long bgjhId, Long hz, String typeId, int pageNo, int pageSize) {
//        Page<Map> page = new Page<Map>();
//        Map<String, Object> param = new HashMap<String, Object>();
//        List<String> condition = new ArrayList<String>();
//        String sqlBase = new String();
//        String sqlColumn = StringUtils.EMPTY;
//        if (hz == 0 && (typeId == null || !typeId.equals("2"))) {
//            sqlColumn = "select mx.id as \"id\", " +
//                    "g.id as \"wzgg.id\", " +
//                    "g.oldcode as \"wzgg.oldcode\", " +
//                    "g.code as \"wzgg.code\", " +
//                    "g.name as \"wzgg.name\", " +
//                    "g.ggxh as \"wzgg.ggxh\", " +
//                    "g.th as \"wzgg.th\", " +
//                    "g.cz as \"wzgg.cz\", " +
//                    "mx.sbcl as \"sbcl\", " +
//                    "'' as \"ckj\", " +
//                    "g.dw as \"dw\", " +
//                    "mx.jhsl as \"jhsl\", " +
//                    "mx.dhrq as \"dhrq\", " +
//                    "(select y.xm from t_sys_yg y,t_biz_bgjh h where mx.bgjh_id=h.id and h.createuser_id=y.id) as \"jhy\"," +
//                    "mx.zt as \"zt\"," +
//                    "mx.ph as \"ph\", "+
//                    "mx.bz as \"bz\" ";
//            sqlBase = "from t_biz_bgjhmx mx " +
//                    "left join t_sys_gg g on mx.wzgg_id=g.id " +
//                    "left join t_sys_sjzdz cd on cd.id=mx.sjzdz_cd_id";
//            if (bgjhId != null) {
//                condition.add(" mx.bgjh_id = :id ");
//                param.put("id", bgjhId);
//            }
//        } else if (hz == 1) {
//            sqlColumn = "select mx.id as \"id\", " +
//                    "g.id as \"wzgg.id\", " +
//                    "g.oldcode as \"wzgg.oldcode\", " +
//                    "g.code as \"wzgg.code\", " +
//                    "g.name as \"wzgg.name\", " +
//                    "g.ggxh as \"wzgg.ggxh\", " +
//                    "g.th as \"wzgg.th\", " +
//                    "(case when mx.cgfs=1 then '比价' else '招标' end) as \"cgfs\"," +
//                    "mx.sbcl as \"sbcl\", " +
//                    "'' as \"ckj\", g.dw as \"dw\", mx.jhsl as \"jhsl\", " +
//                    " NVL((select sum(kh.sl) from t_biz_bgjhhz hz " +
//                    " left join t_biz_kh kh on kh.ck_id=hz.dhck_id and kh.deleted=0 " +
//                    " where hz.id= mx.bgjhhz_id and kh.jtwz_id=mx.wzgg_id),0) \"kcsl\"," +
//                    " nvl(sl1,0) as \"sl1\",nvl(sl2,0) as \"sl2\",(case when nvl(mx.sbsl,0)=0 then mx.jhsl else mx.sbsl end ) as \"sbsl\",mx.thyy  \"thyy\",mx.thrq \"thrq\",thr \"thr\",mx.bz \"bz\",mx.bgjhmx_id \"bgjhmx.id\"," +
//                    " hz.createuser_id \"createUser.id\",gyzclrq \"gyzclsj\",ywkclrq \"ywkclsj\",mx.date_created \"hzsj\"," +
//                    "(select ya.xm from T_SYS_YG ya where mx.gyzclr_id=ya.id) \"gyzclr\"," +
//                    "(select yb.xm from T_SYS_YG yb where mx.YWKCLR_ID=yb.id) \"ywkclr\"," +
//                    "(select y.xm from t_sys_yg y,t_biz_bgjhhz hz where mx.bgjhhz_id=hz.id and hz.createuser_id=y.id and y.deleted=0) as \"hzr\"," +
//                    "(select WMSYS.WM_CONCAT(cgdmx.cgd_id) from T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where  cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1) \"cgdid\"," +
//                    "(select WMSYS.WM_CONCAT(cgd.cgdh) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1) \"cgdh\"," +
//                    "(select WMSYS.WM_CONCAT(yg.xm) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz,T_SYS_YG yg where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and yg.id=cgd.YSR_ID and cgdmx.deleted!=1 and cgd.deleted!=1) \"ysr\"," +
//                    "(select cgd.ysrq from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1 and rownum=1) \"yssj\"," +
//                    "(case when mx.cgfs=1 then " +
//                    "(select WMSYS.WM_CONCAT(htmx.ht_id) from T_BIZ_HTMX htmx,T_BIZ_HTJHDZ htdz where htmx.id=htdz.bghtmxid and htdz.detailid=mx.id)" +
//                    " else (select WMSYS.WM_CONCAT(cgd.cght_id) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1) end) as \"htid\"," +
//                    "(case when mx.cgfs=1 then " +
//                    "(select WMSYS.WM_CONCAT(ht.htbm) from T_BIZ_HT ht,T_BIZ_HTMX htmx,T_BIZ_HTJHDZ htdz where ht.id=htmx.ht_id and htmx.id=htdz.bghtmxid and htdz.detailid=mx.id and ht.deleted!=1 and htmx.deleted!=1)" +
//                    " else (select WMSYS.WM_CONCAT(ht.htbm) from T_BIZ_HT ht,T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where ht.id=cgd.cght_id and cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1 and ht.deleted!=1) end) as \"htbm\"," +
//                    "mx.dhrq as \"dhrq\", mx.ph as \"ph\",mx.zt as \"zt\" ";
//            sqlBase = " from t_biz_bgjhhzmx mx " +
//                    " left join t_sys_gg g on g.id = mx.wzgg_id " +
//                    " left join t_biz_bgjhhz hz on hz.id = mx.bgjhhz_id";
//            if (bgjhId != null) {
//                condition.add(" hz.id = :id ");
//                param.put("id", bgjhId);
//            }
//        } else if (typeId.equals("2")) {
//            sqlColumn = "select mx.id as \"id\", " +
//                    "g.id as \"wzgg.id\", " +
//                    "g.oldcode as \"wzgg.oldcode\", " +
//                    "g.code as \"wzgg.code\", " +
//                    "g.name as \"wzgg.name\", " +
//                    "g.ggxh as \"wzgg.ggxh\", " +
//                    "g.th as \"wzgg.th\", " +
//                    "(case when mx.cgfs=1 then '比价' else '招标' end) as \"cgfs\"," +
//                    "mx.sbcl as \"sbcl\", " +
//                    "'' as \"ckj\", g.dw as \"dw\", mx.jhsl as \"jhsl\", " +
//                    " NVL((select sum(kh.sl) from t_biz_bgjhhz hz " +
//                    " left join t_biz_kh kh on kh.ck_id=hz.dhck_id and kh.deleted=0 " +
//                    " where hz.id= mx.bgjhhz_id and kh.jtwz_id=mx.wzgg_id),0) \"kcsl\"," +
//                    " nvl(sl1,0) as \"sl1\",nvl(sl2,0) as \"sl2\",(case when nvl(mx.sbsl,0)=0 then mx.jhsl else mx.sbsl end ) as \"sbsl\",mx.thyy  \"thyy\",mx.thrq \"thrq\",thr \"thr\",mx.bz \"bz\",mx.bgjhmx_id \"bgjhmx.id\"," +
//                    " hz.createuser_id \"createUser.id\",gyzclrq \"gyzclsj\",ywkclrq \"ywkclsj\",mx.date_created \"hzsj\"," +
//                    "(select ya.xm from T_SYS_YG ya where mx.gyzclr_id=ya.id) \"gyzclr\"," +
//                    "(select yb.xm from T_SYS_YG yb where mx.YWKCLR_ID=yb.id) \"ywkclr\"," +
//                    "(select y.xm from t_sys_yg y,t_biz_bgjhhz hz where mx.bgjhhz_id=hz.id and hz.createuser_id=y.id) as \"hzr\"," +
//                    "(select WMSYS.WM_CONCAT(cgdmx.cgd_id) from T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where  cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1) \"cgdid\"," +
//                    "(select  WMSYS.WM_CONCAT(cgd.cgdh) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1) \"cgdh\"," +
//                    "(select WMSYS.WM_CONCAT(yg.xm) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz,T_SYS_YG yg where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and yg.id=cgd.YSR_ID and cgdmx.deleted!=1 and cgd.deleted!=1) \"ysr\"," +
//                    "(select cgd.ysrq from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1 and rownum=1) \"yssj\"," +
//                    "(case when mx.cgfs=1 then " +
//                    "(select WMSYS.WM_CONCAT(htmx.ht_id) from T_BIZ_HTMX htmx,T_BIZ_HTJHDZ htdz where htmx.id=htdz.bghtmxid and htdz.detailid=mx.id)" +
//                    " else (select WMSYS.WM_CONCAT(cgd.cght_id) from T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and cgdmx.deleted!=1 and cgd.deleted!=1) end) as \"htid\"," +
//                    "(case when mx.cgfs=1 then " +
//                    "(select WMSYS.WM_CONCAT(ht.htbm) from T_BIZ_HT ht,T_BIZ_HTMX htmx,T_BIZ_HTJHDZ htdz where ht.id=htmx.ht_id and htmx.id=htdz.bghtmxid and htdz.detailid=mx.id and htmx.deleted!=1 and ht.deleted!=1)" +
//                    " else (select WMSYS.WM_CONCAT(ht.htbm) from T_BIZ_HT ht,T_BIZ_CGD cgd,T_BIZ_CGDMX cgdmx,T_BIZ_JHCGDDZ dz where ht.id=cgd.cght_id and cgd.id=cgdmx.cgd_id and cgdmx.id=dz.CGDMXID and dz.HZMXID=mx.id and ht.deleted!=1 and cgdmx.deleted!=1 and cgd.deleted!=1) end) as \"htbm\"," +
//                    "mx.dhrq as \"dhrq\",mx.ph as \"ph\", mx.zt as \"zt\" ";
//            sqlBase = " from t_biz_bgjhhzmx mx " +
//                    " left join t_sys_gg g on g.id = mx.wzgg_id " +
//                    " left join t_biz_bgjhhz hz on hz.id = mx.bgjhhz_id";
//            if (bgjhId != null) {
//                condition.add(" hz.id = (select bgjhhz_id from t_biz_bgjh where id=:id) ");
//                param.put("id", bgjhId);
//            }
//        }
//
//        String whereSql = "";
//        if (!condition.isEmpty())
//            whereSql = " where " + StringUtils.join(condition, " and ");
//        String countSql = "select count(1) " + sqlBase + " " + whereSql + " and mx.deleted=0";
//        String resultSql = sqlColumn + " " + sqlBase + " " + whereSql + " and mx.deleted=0  order by g.oldcode";
//        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
//        page.setTotalCount(count.intValue());
//        if (count.intValue() != 0) {
//            List<Map> list = createSQLQuery(resultSql)
//                    .setProperties(param)
//                    .setFirstResult((pageNo - 1) * pageSize)
//                    .setMaxResults(pageSize)
//                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
//                    .list();
//            page.setResult(list);
//        } else {
//            page.setResult(new ArrayList<Map>());
//        }
//        return page;
//    }

    public Page<Map> findBgjhmxByBgjhIdAndHz(Long bgjhId, Long hz, String typeId, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlBase = new String();
        String sqlColumn = StringUtils.EMPTY;
        sqlColumn = "select mx.id as \"id\", " +
                "g.id as \"wzgg.id\", " +
                "g.oldcode as \"wzgg.oldcode\", " +
                "g.code as \"wzgg.code\", " +
                "g.name as \"wzgg.name\", " +
                "g.ggxh as \"wzgg.ggxh\", " +
                "g.th as \"wzgg.th\", " +
                "g.cz as \"wzgg.cz\", " +
                "mx.sbcl as \"sbcl\", " +
                "'' as \"ckj\", " +
                "g.dw as \"dw\", " +
                "mx.sjzdz_cd_id as \"cd.id\","+
                "mx.jhjs as \"jhjs\","+
                "mx.jhsl as \"jhsl\", " +
                "mx.dhrq as \"dhrq\", " +
                "y.xm as \"jhy\"," +
                "mx.zt as \"zt\"," +
                "mx.ph as \"ph\", "+
                "mx.bz as \"bz\" ";
        sqlBase = "from t_biz_@jhmx mx " +
                "left join t_sys_gg g on mx.wzgg_id=g.id " +
                "left join t_biz_@jh h on mx.@jh_id = h.id " +
                "left join t_sys_yg y on h.createuser_id=y.id " +
                "left join t_sys_sjzdz cd on cd.id=mx.sjzdz_cd_id";
        if (bgjhId != null) {
            condition.add(" mx.@jh_id = :id ");
            param.put("id", bgjhId);
        }
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String countSql = "select count(1) " + sqlBase + " " + whereSql + " and mx.deleted=0";
        String resultSql = sqlColumn + " " + sqlBase + " " + whereSql + " and mx.deleted=0  order by g.oldcode";
        countSql = (hz.longValue() == 1 ? countSql.replaceAll("@jh","bgjhhz") : countSql.replaceAll("@jh","bgjh"));
        resultSql = (hz.longValue() == 1 ? resultSql.replaceAll("@jh","bgjhhz") : resultSql.replaceAll("@jh","bgjh"));
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

}
